The program discussed in this chapter is included on the CD-ROM as the Best Team Selector. Figure 36.1 shows the Best Team Selector program main screen. In a small and hopefully interesting way, it demonstrates some of the power that Visual Basic 4.0
brings the programmer. As many developers have discovered, there is always a way to make Visual Basic do what you want.
Figure 36.1. Main screen of the Best Team Selector program.
This chapter will dissect this moderately complex database application to study the use of several VB4 programming techniques and data access methods. In particular, we will take a close look at how this program implements some of the following:
But before we get into those details, let's spend a little time looking at what this program does and doesn't do.
As implied by its name, Best Team Selector (BTS) analyzes a database of candidates and reports a combination that makes a best team to staff a project. Candidates are described by the skills they possess. Projects are described by the skills needed to
tackle them. This program could be used to find a best team to work on a technology project, as illustrated by the sample database supplied with the program, or a best fantasy baseball team, football team, you name it. For instance, you could describe the
skills your favorite baseball players possess, describe the skills a baseball team needs by position, press the Select Best Team button, and presto: You have an awesome fantasy baseball team. The gist is this: If team members can be described by the skills
they possess, and the things the teams do can be described by the skills needed to do them, you can use this program to pick a team for you.
NOTE
In the sample database Frank's Virtual Workshop, many of the skills are technology-oriented, such as VB Programming. Each skill a candidate has is rated on a scale from 1 to 10 with 10 being the best, and 1 being, well, really bad. To see the skills of
our hypothetical candidates, select the Candidates tab and then the Detail subtab. You can view each candidate in the database by pressing the left and right arrows of the data control called Candidate Scroll.
The candidate skills come from a master list of skills available through the Skills tab. Press the Skills tab to switch into that tab page. This is where you define any skill you think might matter. Skills from the master list are assigned to candidates
back on the Candidates tab page. The skills themselves have no rating, only the ability of a candidate to apply a skill is rated.
Job titles are described on the Titles tab screen. A title is just a group of skills lumped together and given a name. The sample database, Frank's Virtual Workshop, defines a software tester as someone who has some skill in English Writing and
Regression Testing.
Projects are just tasks that require people with certain skill sets. In BTS, those skill sets are identified by job title. In the sample database, the Superstore Billing System project requires two Programmer/Analysts, one Computer Graphic Artist, and
one Software Tester.
Pressing the Select Best Team button on the Teams tab screen for the sample Superstore Billing System project comes back with the following list:
Title Name Computer Graphic Artist Mellissa Armona Programmer/Analyst Greg Algol Programmer/Analyst John Derio Software Tester Todd Chelder
Although there are other teams in this database that may be just as good, there is no better team. A program that finds all best teams could be a lot slower to execute than this one, but could be built from the code in this Best Team Selector. The
programming involved to do that is beyond the scope of this presentation.
To begin a new analysis, select New from the file menu. This gives you a blank database. Alternatively, select an existing database and modify it to your needs. For example, you could start with the sample database and alter the candidates to reflect
your scenario if you want to start with the skills already defined there.
If your database does not already have the relevant skills for your projects, start by adding them. Once you have defined most of the skills, figure out the types of jobs members of your team would perform and give each type a name. Each job type is
named and defined in the Titles section of the program. Titles are defined by the skills attributed to them.
At this point you may either enter your candidate information or define your projects. Candidates are described by the information on the Detail subscreen of the Candidate tab page. Skills are attributed to each candidate from the list of skills entered
earlier into the database. Each skill is assigned with a rating, where 10 is the best, and 1 is the worst. Projects are defined by the titles that are needed to work on them.
Once your database is populated with Candidates and Projects, you are ready to find optimal teams. Do this by selecting the Team tab page and then identifying the project of interest from the combo box (see Figure 36.2).
Figure 36.2. The starting of the selection process.
Pressing the Select Best Team button brings up the Settings form that lets you specify whether or not you only want exact skill matches to be considered. This means that if exact skill matches are required, a candidate that is missing any skill defined
for a title will not be considered for that title. This may cause a result where no team can be picked because none of the available candidates can do a particular job by that strict requirement. If exact matches are not required, all candidates are
considered for all jobs. However, for every skill a candidate does not have, a huge disadvantage is attributed to that candidate. The result is that better fitting candidates are more readily considered for the jobs that they fit well, but all jobs will be
filled as long as breathing bodies are available.
Because the BTS program only reports one best team, you can fiddle around a bit with the scenarios by making some employees unavailable for consideration. This is easily done by toggling the Available checkbox for the candidate on the Detail subtab
screen of the Candidate tab page.
Team selections are overwritten every time a new team is selected. When a team is selected, a cost is displayed. This cost value can be used for comparison between teams of the same project. No printing capabilities have been built into this sample
program.
NOTE
Unlike some other languages, such as C/C++ and Pascal, Visual Basic does not support a pointer datatype that you can use for general referencing purposes in your programs.
In the loosest sense, the general concept of a pointer datatype is that it does not contain any data directly that is of particular use to a program, but rather that it points to something that might be of interest. For example, an integer datatype
holds a number that a program can use to store a value of some importance. Conceptually, a pointer might be used to point to that integer. If the program accessed the pointer, it would really just be accessing the value of the integer it is pointing to.
Best Team Selector simulates the functionality of a pointer datatype to pass the user-defined type memjrec_t to and from procedure and functions. Just as a real pointer would tell the program where to find a structure, the index to an array of
user defined types does the same. The reason for doing this is that BTS has to allocate and deallocate memjrec_t nodes as it calculates possible candidate combinations.
The MEM.BAS file (module frmMEM) contains all the procedures used to implement this simulation. VB programmers that take a close look at that module will notice that the allocation of memory is simulated by the simplest means—a sequential search of
the memPool() array. This is okay for small memory sizes, but very inefficient for larger pools. If you want to boost the performance of BTS significantly, this is a good place to spend some time.
A heap in computer science terms is a type of memory structure that keeps its contents sorted. The BTS program does not really implement a heap, as a close look at the code in HEAP.BAS shows, but it does present the interface routines that could wrap a
real heap in VB.
Heaps are usually implemented as linked structures, although they can be implemented in arrays. Simulating linked structures is facilitated by the routines in MEM.BAS.
If you really want to wring performance out of the BTS program, this is also a section that will give a lot of return on time invested. There are many books that explain heap structures. A good university bookstore should have several data structure
books that describe heap implementations.
There are several ways to solve optimization problems like the ones that BTS is built for. A general type of algorithm that could do it is known as back tracking. In this method, a program systematically constructs combinations until it has constructed
every possible one. At that point, the program knows from brute force which combination was the best. That's great only if you have very few members to experimentally combine.
To get an idea how analyzing all combinations can quickly become an intractable problem, look at the numbers in the following table.
Number of Candidates |
Size of a Team |
Total Combinations |
|
1 |
1 |
1 |
|
2 |
1 |
2 |
|
4 |
2 |
4 |
|
8 |
2 |
28 |
|
8 |
4 |
70 |
|
16 |
2 |
120 |
|
16 |
4 |
1,820 |
|
16 |
8 |
12,870 |
|
32 |
2 |
496 |
|
32 |
4 |
35,960 |
|
32 |
8 |
10,518,300 |
|
32 |
16 |
601,080,390 |
|
64 |
32 |
approx. 18x10^18 |
This table is only meant to give an idea of how bulky the problem can become. In reality, calculating the number of possible team member combinations is more complicated. Because the BTS program allows a candidate to qualify for more than one type of
position, the total number of combinations can actually be substantially more than indicated in the table. In fact, the actual number of team solutions can be more like a permutation calculation than a combination calculation. To illustrate what this can
mean, the permutations of 64 candidates where there are 32 on a team is approximately 48x10^51. (That's 48 followed by 51 zeros! Translation: Man that's a big number.)
The Branch and Bound algorithm reduces the number of combinations it builds by taking smart guesses at which might prove fruitful, and which might not. A well-designed Branch and Bound implementation will not waste much time on bad combinations. Because
fewer combinations are sampled, the Branch and Bound can be faster than the Backtracking alternative. In some cases, this can mean the difference between an answer in a million years and an answer within the hour.
A very simplified Branch and Bound algorithm looks like this:
The BB() function in the MINIMIZE.BAS file implements those steps. Step 1 is carried out by function AnswerNode(). Step 3 is carried out by function Expand(). Let's get an overview of how these functions do their jobs.
The AnswerNode() function determines if the current candidate was the last possible addition to a team by comparing its job count number to the total number of jobs required on the team. Because the program keeps track of team additions by incrementing
a job count, when these figures match, the team is filled. The Branch and Bound algorithm only builds teams that can be the best, so the fact that we have a complete team implies we have a best team.
The Expand() function is more difficult to understand. The loosest explanation is that it creates a node representing the addition of another team member for each possible type of team member addition at that particular point in time. Of special
significance, and where the power of the Branch and Bound algorithm springs, is that each new node is assigned an estimated cost. The estimated cost of each node determines whether the algorithm will pursue combinations derived from that node.
Estimating the cost can be a very complex topic in its own right and there are many ways to do it. The BTS program approaches the task by figuring a matrix of each candidate vs. each job. This matrix contains the cost of the candidate doing every job.
Because the engine of this program is designed to minimize the total cost of a team and the inputs specify 10 as better than 9 and so forth, the higher numbers are translated to lower numbers up front by the CalcCost() function. The job cost estimates are
then computed by taking the minimum value of each row, where each row of the matrix represents a particular job on the team. The InitMinsTable() carries out this process.
The logic of applying the minimum values to predict the total cost is this: If we add up all the minimums from a filled job down to the last job, we have an estimate that can be no worse than any real result. Since the real cost is never less than this
sum-based estimate, a large minimum sum indicates a poor combination choice.
Nodes are kept in memory, ready for plucking by lowest cost. Using a heap for this is a good solution because we can efficiently find the lowest cost node when we need it. We pluck the lowest cost node to figure new team possibilities and then repeat
the process. By this means we end up constructing a team that can be no worse than any other.
This trek only superficially explains the Branch and Bound concept. For a detailed explanation and discussions of related concepts that are touched on here, you should consider purchasing a good fundamental algorithms book. Most university and college
bookstores carry them.
There are two general form management approaches. A popular choice is the Multiple Document Interface, MDI method, where the program forms are contained within a parent form. Alternatively, a program can go the Single Document Interface, SDI, route as
this one does.
An SDI interface, more than an MDI, requires the programmer to decide the following basic construction/flow items up front:
In BTS, the answer to the first question is yes, and the answer to the second is no. This is a relatively easy way to implement a VB program. BTS implements the results to preceding questions 1 and 2 by following these rules:
As illustrated by the single line of the HelpAbout_Click() function in the frmMain module:
frmAbout.Show 1
The 1 after the method keyword Show forces VB to wait until that form is no longer visible before allowing input to other forms. In other words, frmAbout is a modal form. All the sub-forms of this application are loaded in the same way (see Figure
36.3).
Figure 36.3. The ABOUT form selected from the HELP menu.
As with most programs heavy on database access and loaded with custom controls, BTS can take a little while to load. Few things confuse some users more than starting a program and then seeing nothing happen for several seconds or more. In the old days,
that was enough to make some people reboot.
The most common way to entertain the user while their program loads is to paint a Splash screen first and then start loading and setting up whatever program modules are to do business with the user. Making this type of screen and displaying it first is
fun and easy in VB4. Here's how it's done in BTS:
Figure 36.4. The SPLASH screen at design time.
That's all there is to it. Just keep in mind that the splash screen should not have too many controls or be very large because this might slow down its load time. It would be silly to need a splash screen for your splash screen.
Because it's easy. Easy to implement, and —okay, some people may not agree with this—easy to use. Each tabbed screen in this program could have been implemented as a separate form but that would have required code to manage those forms.
Instead, each screen is just laid onto a pane of an SSTab control. The tab control does the display management for you.
Some users love tabs, some hate tabs. Some folks are not sure if they love or hate them. One thing is for sure however, we will see more tabs because Windows 95 uses them throughout its utilities. My guess is that as people become more familiar with
using this interface, they will move on to other areas to complain about. Users always have to complain.
To see how the main tab control is configured in this program, press the right mouse button over the Candidates tab while in design mode and pick properties from the popup menu that appears. This displays the Sheridan Tabbed Dialog Control Properties
interface, which is not the same one you get when you press F4 on a selected SSTab but is more intuitive to work with. You see here that each tab screen has a TabCaption, which is the text you see at the bottom of the BTS program. The number of tabs
supported by the control is simply set by the Tab Count property. If you view the properties of the tab control nested into the Candidates tab screen with the Detail and Summary tab labels, you will notice that its Style is set to the Windows 95 Property
Page so that the tab labels bunch over to the left. The main tab control spreads its tab labels across the entire width of the screen because there is room to do it. Likewise, the tabs in the Skills of Candidate area of this same screen cover the width of
the tab page section. The full spread style is Microsoft Office Tabbed Dialog, named after the fact that this style is employed in the Microsoft office software that began shipping back in 1994.
When building your programs, treat each tab screen as a form in the sense that it contains the controls you wish to use. Drop 'em, size 'em, configure 'em, and move onto the next tab screen. It's that simple.
At runtime, you can tell what tab the user is on by reading the TAG property of your Tab control. The tab values will match those visible in the property display at design time. The SSTab control on the Candidate Detail tab page has the following code
on the Click event:
Private Sub tabCandidateSkill_Click(PreviousTab As Integer)
Dim thekey
thekey = lstCandidateSkill.BoundText
Select Case tabCandidateSkill.Tab
Case 0
'List
If Len(CStr(txtCandidateKey)) > 0 Then
If txtCandidateKey > -1 Then 'Flag that no candidate is selected.
'We have a candidate so list his/her skills.
On Error Resume Next
dbaCandidateSkill.RecordSource = "Select * From qryCandidateSkillRating_ Where CandidateKey=" + txtCandidateKey
dbaCandidateSkill.Recordset.Requery
dbaCandidateSkill.UpdateControls
lstCandidateSkill.Refresh 'Due to Beta6 trouble.
dbaCandidateSkill.Refresh 'Due to Beta6 trouble.
End If
End If
Case 1 'Add
'Add
cboAddSkill = ""
txtAddRating = gDefaultRating
Case 2 'Edit
'Get the current record.
If IsNull(thekey) Or Len(Trim$(thekey)) < 1 Then
MsgBox "No candidate skill is selected.", 64, gProgramTitle
tabCandidateSkill.Tab = 0
Else
dbaCandidateSkill.Recordset.FindFirst "CandidateSkillsKey=" + Str$(thekey)
txtEditSkill = dbaCandidateSkill.Recordset![Name]
txtEditRating = dbaCandidateSkill.Recordset![Rating]
End If
Case 3
'Delete
If IsNull(thekey) Or Len(Trim$(thekey)) < 1 Then
MsgBox "No candidate skill is selected.", 64, gProgramTitle
tabCandidateSkill.Tab = 0
Else
dbaCandidateSkill.Recordset.FindFirst "CandidateSkillsKey=" + Str$(thekey)
End If
End Select
End Sub
When the Click event is triggered, the Tab value is already set to the new page. Although the preceding code example does not need this, you can tell where the user was before by reading the value of the PreviousTab parameter. This is a feature not
readily available when using separate forms instead of a tab interface.
Figure 36.5 shows the tables and relationships of the database used by the BTS program. The relationships among tables are represented by the lines connecting the matching fields. A relationship of one-to-many is represented by a number 1 on the one
side and an infinity symbol on the many side.
Figure 36.5. MAKETEAM database relationship diagram.
Tip
The candidate records are in their own table. Likewise, there is a table for skills and a table for projects. Connecting these tables are linking tables that relate the contents of the tables to each other. For example, tblCandidateSkill is a table that
links skills from tblSkill to candidates from tblCandidate. Figures 36.6 through 36.8 show the structure detail of these particular tables.
Figure 36.6. Detail of Candidates table.
Figure 36.7. Detail of Skills table.
Figure 36.8. Detail of linking table for candidates and their skills.
Although the BTS program uses an Access 2.0 database structure, there is no reason this database could not be implemented in any number of other schemes. However, Access has an advantage within VB that it is handled directly by the Jet engine and thus
runs faster than some others.
Although some database access is done in BTS through dynamically declared data objects, most of the access is handled through VB Data controls. These controls have the advantage of convenience.
At design time, the DatabaseName property of every Data control was left blank. Otherwise, the program will try to load a database at whatever directory was used during development. Instead of hardcoding a directory, the program sets the datasource of
all the main Data controls to the application directory as follows:
'———————————————————————————— 'Open the selected database. Returns FALSE if error. '———————————————————————————— Public Function SetDatabase(AP As String) As Boolean On Error GoTo SetDatabase_SomethingBad 'Open main database. gMainDBName = AP Set gMainDB = Workspaces(0).OpenDatabase(gMainDBName) 'Adjust the data controls to proper path. dbaCandidate.DatabaseName = gMainDBName dbaCandidateSkill.DatabaseName = gMainDBName dbaSkills.DatabaseName = gMainDBName dbaJobProject.DatabaseName = gMainDBName dbaProject.DatabaseName = gMainDBName dbaJobtitle.DatabaseName = gMainDBName dbaCandidateProject.DatabaseName = gMainDBName dbaSkillJob.DatabaseName = gMainDBName 'Refresh all the controls. dbaCandidate.Refresh dbaCandidateSkill.Refresh dbaSkills.Refresh dbaJobProject.Refresh dbaProject.Refresh dbaJobtitle.Refresh dbaCandidateProject.Refresh dbaSkillJob.Refresh SetDatabase = True 'Successful. Exit Function SetDatabase_SomethingBad: SetDatabase = False 'Unsuccessful. End Function
The SetDatabase function is called both at startup with the default database name of MAKETEAM passed as the parameter. It also runs when the user elects to open a new database. When it runs, this function sets a global dynamic data access object called
gMainDB to the selected database to simplify ad-hoc data access throughout the program. In particular, the GENSQL.BAS module contains three general purpose data access routines that are usually called with gMainDB as a parameter. These routines are as
follows:
'——————————————————————————————————
'Returns number of records in set defined by the "From" and "Where".
'——————————————————————————————————
Function SQLResultCount(MyDB As DATABASE, From$, Where$) As Long
Dim MyData As Recordset, SQL$
SQL$ = "Select count(*) as [total] from " + From$ + _
IIf(Len(Trim$(Where$)) > 0, " where " + Where$, "")
Set MyData = MyDB.OpenRecordset(SQL$, dbOpenDynaset)
If MyData.RecordCount = 0 Then
SQLResultCount = 0
Else
MyData.MoveLast
SQLResultCount = MyData![total]
End If
End Function
'——————————————————————————————————
'Returns value of field named in "aField$" of "MyDB" after applying the
'"From" and "Where" portions of the SQL statement.
'This only returns the value in last record. If the record is not
'found, the empty string is returned.
'——————————————————————————————————
Function SQLResultStr(MyDB As DATABASE, aField$, From$, Where$) As String
Dim MyData As Recordset, SQL$
SQL$ = "Select " + aField + " as [result] from " + From$ + _
IIf(Len(Trim$(Where$)) > 0, " where " + Where$, "")
Set MyData = MyDB.OpenRecordset(SQL$, dbOpenDynaset)
If MyData.RecordCount = 0 Then
SQLResultStr = "" 'Send empty string.
Else
MyData.MoveLast
SQLResultStr = MyData![result]
End If
End Function
'——————————————————————————————————
'Returns sum of values in "SumField" of "MyDB" after applying the
'"From" and "Where" portions of the SQL statement.
'——————————————————————————————————
Function SQLResultSum(MyDB As DATABASE, SumField As String, From$, Where$) As Long
Dim MyData As Recordset, SQL$
SQL$ = "Select sum(" + SumField + ") as [total] from " + From$ + _
IIf(Len(Trim$(Where$)) > 0, " where " + Where$, "")
Set MyData = MyDB.OpenRecordset(SQL$, dbOpenDynaset)
If MyData.RecordCount = 0 Then
SQLResultSum = 0 'Send empty string.
Else
MyData.MoveLast
SQLResultSum = MyData![total]
End If
End Function
These general purpose functions require just a few parameters to carry out some otherwise tediously coded syntax queries. The MINIMIZE.BAS module uses them extensively for data processing. In the case of BTS, the MyDB parameter is always set to the
gMainDB data access object.
The BTS program has several instances of lists that control other lists. For example, the Titles tab page has a list of existing titles on the left and a list of skills for the selected title at the right. The synchronization is done by using a Text box
as follows:
One issue to watch out for when using this synchronization method is that VB will trigger the Change event of the text box as it loads the form. To keep your code from trying to synchronize a list with an invalid key value, you may want to place a flag
value, such as -1, into the text box at design time. Then at runtime, you can check for this value and exit your processing during the form load.
Although most of the DBLists could have been implemented with data-aware Grid controls, none were available at the time this program was written. However, if you have access to DBGrid controls you may want to use them in your projects instead of DBList.
The DBGrid control dynamics are very similar to those of the DBList, but they display multiple columns with greater ease.
Visual Basic 4.0 can directly bind the Image and Picturebox controls to an OLE type BLOB field for graphic display and entry. The advantage of using the Image control is that it uses fewer system resources and displays faster than the picture control.
The disadvantage is that it does not crop the input image. To keep odd-size images from spilling into the form space, BTS uses the Picturebox control.
Picture setting in BTS is done through the Copy/Paste method. To add a picture to a Candidate record in BTS, follow these steps:
The code that implements this functionality also handles the cut/copy/paste functionality for all the other controls in the BTS program. The subroutines from BTS are attached to the menu and are as follows:
Private Sub editCut_Click()
' First do the same as a copy.
editCopy_Click
' Now clear contents of active control.
If TypeOf Screen.ActiveControl Is TextBox Then
Screen.ActiveControl.SelText = ""
ElseIf TypeOf Screen.ActiveControl Is ComboBox Then
Screen.ActiveControl.TEXT = ""
ElseIf TypeOf Screen.ActiveControl Is PictureBox Then
Screen.ActiveControl.picture = LoadPicture()
ElseIf TypeOf Screen.ActiveControl Is ListBox Then
Screen.ActiveControl.RemoveItem Screen.ActiveControl.ListIndex
Else
' No action makes sense for the other controls.
End If
End Sub
Private Sub editCopy_Click()
Clipboard.Clear
If TypeOf Screen.ActiveControl Is TextBox Then
Clipboard.SetText Screen.ActiveControl.SelText
ElseIf TypeOf Screen.ActiveControl Is ComboBox Then
Clipboard.SetText Screen.ActiveControl.TEXT
ElseIf TypeOf Screen.ActiveControl Is PictureBox Then
Clipboard.SetData Screen.ActiveControl.picture
ElseIf TypeOf Screen.ActiveControl Is ListBox Then
Clipboard.SetText Screen.ActiveControl.TEXT
Else
' No action makes sense for the other controls.
End If
End Sub
Private Sub editPaste_Click()
If TypeOf Screen.ActiveControl Is TextBox Then
Screen.ActiveControl.SelText = Clipboard.GetText()
ElseIf TypeOf Screen.ActiveControl Is ComboBox Then
Screen.ActiveControl.TEXT = Clipboard.GetText()
ElseIf TypeOf Screen.ActiveControl Is PictureBox Then
Screen.ActiveControl.picture = Clipboard.GetData()
ElseIf TypeOf Screen.ActiveControl Is ListBox Then
Screen.ActiveControl.AddItem Clipboard.GetText()
Else
' No action makes sense for the other controls.
End If
End Sub
The naming convention for menus in the BTS program is to prefix the menu options with the menu name. So, editCopy_Click is the Click event handler for the Copy function of the Edit menu.
The BTS program applies some complex coding concepts to identify an optimal project team. Some explanation of the Branch and Bound algorithm and other computer science esoterica are explored here as they apply to Visual Basic 4.0, although in brief
form.
The chapter also covers more bread and butter Visual Basic 4.0 development items implemented in BTS, such as employing a splash screen and accessing a database through code and data controls. These and other topics were discussed with examples.